﻿/*-----------------------------------------------------------------------
 * <copyright file="DataProvider.cs" company="O-RID">
 *     Copyright (c)2010 O-RID Co.,Ltd.
 * </copyright>
 * <author>Zhang Jun</author>
 * <summary>
 *          所有与 账票类、工程类 信息操作相关的所有服务的定义
 *          WCFMatterCorrelation.svc服务实现的具体方法
 *          Get（Set）+服务方法名
 *          作成日：08/06/2010
 *                  08/13/2010 增加 JOB化率查询
 *                  08/25/2010 增加 帳票別情報
 *                  09/09/2010 增加 帳票工程別情報 查询
 *                  10/21/2010 修改 综合检索里所有服务，当查询一天时间时的数据存储方法
 *                              
 * </summary>
 *-----------------------------------------------------------------------
 */
namespace Orid.Management.Statistics.Service.OPUnitService.MatterCorrelation
{
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using Orid.Management.Statistics.Service.OPUnitService.MatterCorrelation.ServiceModel;

    /// <summary>
    /// DataProvider
    /// 具体方法
    /// </summary>
    public class DataProvider
    {
        #region 工程別言語別生産性
        /// <summary>
        /// 工程別言語別生産性
        /// </summary>
        /// <param name="whereParameter">The where parameter.</param>
        /// <returns></returns>
        public static List<EngineeringLanguageProData> GetSelectEngineeringLanguageProQueryInfor(WhereParameters whereParameter)
        {
            List<EngineeringLanguageProData> retData = new List<EngineeringLanguageProData>();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand("ReportEntrySpeedQuery", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starTime", whereParameter.WhereDateFrom));
                    cmd.Parameters.Add(new SqlParameter("@endTime", whereParameter.WhereDateTo));

                    cmd.Parameters.Add(new SqlParameter("@Server", SqlDbType.NVarChar));
                    cmd.Parameters["@Server"].Value = whereParameter.WhereServer;

                    cmd.CommandTimeout = 50000;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        EngineeringLanguageProData addDate = new EngineeringLanguageProData();
                        
                        addDate.MatterName = Common.MyIsDBNull(reaccount, "MatterName");
                        addDate.ScriptType = Common.MyIsDBNull(reaccount, "ScriptType");
                        addDate.JobClass = Common.MyIsDBNull(reaccount, "JobClass");
                        addDate.WordType = Common.MyIsDBNull(reaccount, "WordType");
                        addDate.Project = Common.MyIsDBNull(reaccount, "Project");
                        addDate.AVG = Common.MyToFormat(reaccount, "AVG", Common.ClassType.DOUBLE, "#,##0.00");

                        // 获取 开始日期
                        DateTime getDateStartTime = Convert.ToDateTime(whereParameter.WhereDateFrom);

                        ///对日期字段进行 动态 添加
                        ///相差天数是 0 是同一天
                        if (whereParameter.DateDiffs == 0)
                        {
                            addDate.OutDateTime.Add(0, Common.MyToFormat(reaccount, getDateStartTime.ToString("yyyy-MM-dd"), Common.ClassType.DOUBLE, "#,##0.00"));
                        }
                        else 
                        {
                             for (int i = 0; i < whereParameter.DateDiffs; i++) 
                            {
                                // 将对应的日期项内容，放入到 Dictionary 中
                                addDate.OutDateTime.Add(i, Common.MyToFormat(reaccount, getDateStartTime.ToString("yyyy-MM-dd"), Common.ClassType.DOUBLE, "#,##0.00"));

                                getDateStartTime = getDateStartTime.AddDays(1); 
                            }
                        }
                        retData.Add(addDate);
                    }

                    reaccount.Close();
                    conn.Close();
                }

                return retData;
                #endregion SqlCommand
            }
        }

        #endregion 工程別言語別生産性

        #region JOB化率查询

        /// <summary>
        /// JOB化率查询
        /// </summary>
        /// <param name="whereParameter">The where parameter.</param>
        /// <returns></returns>
        /// <value> 
        ///         08/13/2010 增加 JOB化率查询
        ///         08/16/2010 增加 DJobChart/EJobChart 属性（图表数据）
        ///         12/21/2010 增加 语言分类（WordType）属性
        /// </value>
        public static List<JobRateData> GetSelectJobRateQueryInfor(WhereParameters whereParameter) 
        {
            List<JobRateData> retJobRateList = new List<JobRateData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
                //ReportJobRateQuery

                using (SqlCommand cmd = new SqlCommand("[ReportJobRateQueryWord]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starTime", whereParameter.WhereDateFrom));
                    cmd.Parameters.Add(new SqlParameter("@endTime", whereParameter.WhereDateTo));

                    cmd.Parameters.Add(new SqlParameter("@Server", SqlDbType.NVarChar));
                    cmd.Parameters["@Server"].Value = whereParameter.WhereServer;

                    cmd.CommandTimeout = 50000;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        JobRateData addDate = new JobRateData();

                        
                        addDate.MatterName = Common.MyIsDBNull(reaccount, "MatterName");
                        addDate.JobClass = Common.MyIsDBNull(reaccount, "JobClass");
                        addDate.WordType = Common.MyIsDBNull(reaccount, "WordType"); // 12/21/2010 增加 语言分类（WordType）属性

                        // 获取 开始日期
                        DateTime getDateStartTime = Convert.ToDateTime(whereParameter.WhereDateFrom);

                        //对日期字段进行 动态 添加
                        //相差天数是 0 是同一天
                        if (whereParameter.DateDiffs == 0)
                        {
                            //addDate.OutDateTime.Add(0, Common.MyIsDBNull(reaccount, getDate.ToString("yyyy-MM-dd")));
                            string str = Common.MyIsDBNull(reaccount, getDateStartTime.ToString("yyyy-MM-dd"));

                            //Djob 和 Ejob 加上百分号
                            if (addDate.JobClass.Trim().ToUpper().Equals("EJOB") || addDate.JobClass.Trim().ToUpper().Equals("DJOB"))
                            {
                                
                                #region 08/16/2010 增加 DJobChart/EJobChart 属性（图表数据 构成）
                                switch (addDate.JobClass.Trim().ToUpper())
                                {
                                    case "EJOB":
                                        addDate.EJobChart.Add(0, str);
                                        break;
                                    case "DJOB":
                                        addDate.DJobChart.Add(0, str);
                                        break;
                                }
                                #endregion  08/16/2010 增加 DJobChart/EJobChart 属性（图表数据 构成）

                                //Djob 和 Ejob 加上百分号
                                if (!string.IsNullOrEmpty(str))
                                {
                                    str += "%";
                                }
                                addDate.OutDateTime.Add(0, str);
                            }
                            //其余的用千分位分割符
                            else
                            {
                                addDate.OutDateTime.Add(0, Common.MyToFormat(reaccount, getDateStartTime.ToString("yyyy-MM-dd"), Common.ClassType.DOUBLE, "#,###"));
                            }

                        }
                        //相差天数大于 0 ,是N天
                        else
                        {
                            string str = null;

                            for (int i = 0; i < whereParameter.DateDiffs; i++)
                            {
                                //Djob 和 Ejob 加上百分号
                                if (addDate.JobClass.Trim().ToUpper().Equals("EJOB") || addDate.JobClass.Trim().ToUpper().Equals("DJOB"))
                                {
                                    str = string.Empty;
                                    str = Common.MyIsDBNull(reaccount, getDateStartTime.ToString("yyyy-MM-dd"));

                                    #region 08/16/2010 增加 DJobChart/EJobChart 属性（图表数据 构成）
                                    switch (addDate.JobClass.Trim().ToUpper())
                                    {
                                        case "EJOB":
                                            addDate.EJobChart.Add(i, str);
                                            break;
                                        case "DJOB":
                                            addDate.DJobChart.Add(i, str);
                                            break;
                                    }
                                    #endregion  08/16/2010 增加 DJobChart/EJobChart 属性（图表数据 构成）

                                    //Djob 和 Ejob 加上百分号
                                    if (!string.IsNullOrEmpty(str)) 
                                    {
                                        str += "%";
                                    }
                                    addDate.OutDateTime.Add(i, str);
                                }
                                //其余的用千分位分割符
                                else  
                                {
                                    addDate.OutDateTime.Add(i, Common.MyToFormat(reaccount, getDateStartTime.ToString("yyyy-MM-dd"), Common.ClassType.DOUBLE, "#,###"));
                                }
                                getDateStartTime = getDateStartTime.AddDays(1);
                            }
                        }

                        //Average
                        //当Djob 和 Ejob 时 Average加上百分号
                        if (addDate.JobClass.Trim().ToUpper().Equals("EJOB") || addDate.JobClass.Trim().ToUpper().Equals("DJOB"))
                        {
                            string strAverage = string.Empty;
                            strAverage  = Common.MyIsDBNull(reaccount, "Average");
                            if (!string.IsNullOrEmpty(strAverage))
                            {
                                strAverage += "%";
                            }
                            addDate.Average = strAverage;
                        }
                        //其余的用千分位分割符
                        else
                        {
                            addDate.Average = Common.MyToFormat(reaccount, "Average", Common.ClassType.DOUBLE, "#,###");
                        }
                        retJobRateList.Add(addDate);
                    }
                    reaccount.Close();
                    conn.Close();
                }
                return retJobRateList;
                #endregion SqlCommand
            }
        }
        #endregion JOB化率查询

        #region 稼动率查询
        /// <summary>
        ///稼动率查询
        /// </summary>
        /// <param name="whereParameter">The where parameter.</param>
        /// <returns></returns>
        /// <value>08/20/2010 -- ADD </value>
        public static List<WorkEfficiencyQueryData> GetSelectReportWorkEfficiencyQueryInfor(WhereParameters whereParameter) 
        {
            List<WorkEfficiencyQueryData> retWorkEfficiencyList = new List<WorkEfficiencyQueryData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand("ReportWorkEfficiencyQuery", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starDate", whereParameter.WhereDateFrom));
                    cmd.Parameters.Add(new SqlParameter("@FinishDate", whereParameter.WhereDateTo));

                    cmd.CommandTimeout = 50000;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        WorkEfficiencyQueryData addDate = new WorkEfficiencyQueryData();

                        addDate.Project = Common.MyIsDBNull(reaccount, "Project");

                        //对日期字段进行 动态 添加
                        //相差天数是 0 ,是同一天
                        if (whereParameter.DateDiffs == 0)
                        {
                            addDate.OutDateTime.Add(0, Common.MyIsDBNull(reaccount, whereParameter.WhereDateFrom));
                        }
                        else
                        {
                            /* 出勤時間(H)
                             * 処理時間(H)
                             * 入力時間(H)
                             * Dl,UP時間(H)
                             * 稼働率(処理時間)%
                             * 稼働率(入力時間)%
                             * 比率(Dl,UP時間)%
                             * ----------------
                             * AVG
                             */

                            // 获取 查询的开始日期
                            DateTime getDate = Convert.ToDateTime(whereParameter.WhereDateFrom);
                            string str = null;

                            for (int i = 0; i < whereParameter.DateDiffs; i++)
                            {
                                str = string.Empty;
                                //按日期取数据
                                str = Common.MyIsDBNull(reaccount, getDate.ToString("yyyy-MM-dd"));

                                // 图表数据 构成
                                // 稼动率 \出勤时间 \处理时间 属性（图表数据没有%）
                                switch (addDate.Project.Trim().ToUpper())
                                {
                                    //出勤时间
                                    case "出勤時間(H)":
                                        addDate.AttendanceTimeChart.Add(i, str);
                                        break;

                                    //处理时间
                                    case "処理時間(H)":
                                        addDate.ProcessingTimeChart.Add(i, str);
                                        break;

                                    //稼动率
                                    case "稼働率(処理時間)":
                                        addDate.WrokEffChart.Add(i, str);
                                        break;
                                }

                                // 日期数据 构成
                                // 稼働率(処理時間)\稼働率(入力時間)\比率(Dl,UP時間) 三项内容加%(日期数据)
                                if (addDate.Project.Contains("稼働率") || addDate.Project.Contains("比率"))
                                {
                                    //稼働率(処理時間)\稼働率(入力時間)\比率(Dl,UP時間)+%
                                    if (!string.IsNullOrEmpty(str))
                                    {
                                        str += "%";
                                    }

                                    addDate.OutDateTime.Add(i, str);
                                }
                                //其余的用千分位分割符
                                else
                                {
                                    addDate.OutDateTime.Add(i, Common.MyToFormat(reaccount, getDate.ToString("yyyy-MM-dd"), Common.ClassType.DOUBLE, "#,##0.00"));
                                }

                                getDate = getDate.AddDays(1);
                            }
                        }

                        /* Average
                         * 每一行，只有一个Average，所以在日期循环之外添加
                         * 当是“稼働率(処理時間)\稼働率(入力時間)\比率(Dl,UP時間)”这三项 时 Average加上百分号
                         */
                        if (addDate.Project.Contains("稼働率") || addDate.Project.Contains("比率"))
                        {
                            string strAverage = string.Empty;
                            strAverage = Common.MyIsDBNull(reaccount, "AVG");
                            if (!string.IsNullOrEmpty(strAverage))
                            {
                                strAverage += "%";
                            }

                            addDate.Average = strAverage;
                        }
                        //其余的用千分位分割符
                        else
                        {
                            addDate.Average = Common.MyToFormat(reaccount, "AVG", Common.ClassType.DOUBLE, "#,##0.00");
                        }

                        retWorkEfficiencyList.Add(addDate);
                    }

                    reaccount.Close();
                    conn.Close();
                }
                return retWorkEfficiencyList;
                #endregion SqlCommand
            }
        }

        #endregion 稼动率查询

        #region 帳票別情報
        /// <summary>
        /// 帳票別情報 
        /// </summary>
        /// <param name="whereParameter">The where parameter.</param>
        /// <returns></returns>
        /// <value>08/25/2010 增加</value>
        public static List<FormInforQueryData> GetSelectFormInforQueryInfor(WhereParameters whereParameter) 
        {
            List<FormInforQueryData> retFormInforList = new List<FormInforQueryData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand("[ReportFormInformationQuery]", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starTime", whereParameter.WhereDateFrom));
                    cmd.Parameters.Add(new SqlParameter("@endTime", whereParameter.WhereDateTo));

                    cmd.Parameters.Add(new SqlParameter("@Server", SqlDbType.NVarChar));
                    cmd.Parameters["@Server"].Value = whereParameter.WhereServer;

                    cmd.Parameters.Add(new SqlParameter("@arg", SqlDbType.Float));
                    cmd.Parameters["@arg"].Value = whereParameter.Count;

                    cmd.CommandTimeout = 50000;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        FormInforQueryData addDate = new FormInforQueryData();

                        addDate.MatterName = Common.MyIsDBNull(reaccount, "MatterName");
                        addDate.KindName = Common.MyIsDBNull(reaccount, "KindName");

                        // 获取 开始日期
                        DateTime getDateStartTime = Convert.ToDateTime(whereParameter.WhereDateFrom);

                        //对日期字段进行 动态 添加
                        //相差天数是 0 是同一天
                        if (whereParameter.DateDiffs == 0)
                        {
                            string str = Common.MyIsDBNull(reaccount, getDateStartTime.ToString("yyyy-MM-dd"));
                            //图表数据 构成
                            #region 图表数据 构成
                            if (!addDate.MatterName.Equals("仕分け"))
                            {
                                switch (addDate.KindName.Trim().ToUpper())
                                {
                                    case "1枚コスト":
                                        addDate.OneCountChart.Add(0, Common.MyIsDBNull(reaccount, getDateStartTime.ToString("yyyy-MM-dd")));
                                        break;

                                    case "1文字コスト":
                                        addDate.OneWordCountChart.Add(0, Common.MyIsDBNull(reaccount, getDateStartTime.ToString("yyyy-MM-dd")));
                                        break;
                                }
                            }
                            #endregion 图表数据 构成

                            //日期数据 构成
                            addDate.OutDateTime.Add(0, Common.MyToFormat(reaccount, getDateStartTime.ToString("yyyy-MM-dd"), Common.ClassType.DOUBLE, "#,##0.00"));
                        }
                        else
                        {
                            for (int i = 0; i < whereParameter.DateDiffs; i++)
                            {
                                //图表数据 构成
                                #region 图表数据 构成
                                if (!addDate.MatterName.Equals("仕分け"))
                                {
                                    switch (addDate.KindName.Trim().ToUpper())
                                    {
                                        case "1枚コスト":
                                            addDate.OneCountChart.Add(i, Common.MyIsDBNull(reaccount, getDateStartTime.ToString("yyyy-MM-dd")));
                                            break;

                                        case "1文字コスト":
                                            addDate.OneWordCountChart.Add(i, Common.MyIsDBNull(reaccount, getDateStartTime.ToString("yyyy-MM-dd")));
                                            break;
                                    }
                                }
                                #endregion 图表数据 构成

                                //日期数据 构成
                                addDate.OutDateTime.Add(i, Common.MyToFormat(reaccount, getDateStartTime.ToString("yyyy-MM-dd"), Common.ClassType.DOUBLE, "#,##0.00"));

                                getDateStartTime = getDateStartTime.AddDays(1);
                            }
                        }

                        //Average
                        addDate.Average = Common.MyToFormat(reaccount, "Average", Common.ClassType.DOUBLE, "#,##0.00");

                        retFormInforList.Add(addDate);
                    }
                    reaccount.Close();
                    conn.Close();
                }
                return retFormInforList;
                #endregion SqlCommand
            }
        }
        #endregion 帳票別情報

        #region 帳票工程別情報
        /// <summary>
        /// 帳票工程別情報
        /// </summary>
        /// <param name="whereParameter">The where parameter.</param>
        /// <returns></returns>
        /// <value>09/09/2010 增加 </value>
        public static List<FormInforBasedData> GetSelectFormProjectQueryInfor(WhereParameters whereParameter) 
        {
            List<FormInforBasedData> retFormInforBasedDataList = new List<FormInforBasedData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StatisticsToolsDB"].ConnectionString))
            {
                conn.Open();

                #region "SqlCommand"
                using (SqlCommand cmd = new SqlCommand("ReportFormJobClassInformationQuery", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@starTime", whereParameter.WhereDateFrom));
                    cmd.Parameters.Add(new SqlParameter("@endTime", whereParameter.WhereDateTo));

                    cmd.Parameters.Add(new SqlParameter("@Server", SqlDbType.NVarChar));
                    cmd.Parameters["@Server"].Value = whereParameter.WhereServer;

                    cmd.Parameters.Add(new SqlParameter("@arg", SqlDbType.Float));
                    cmd.Parameters["@arg"].Value = whereParameter.Count;

                    cmd.CommandTimeout = 50000;

                    SqlDataReader reaccount = cmd.ExecuteReader();

                    while (reaccount.Read())
                    {
                        FormInforBasedData addDate = new FormInforBasedData();

                        addDate.MatterName = Common.MyIsDBNull(reaccount, "MatterName");
                        addDate.KindName = Common.MyIsDBNull(reaccount, "KindName");


                        // 获取 开始日期
                        DateTime getDateStartTime = Convert.ToDateTime(whereParameter.WhereDateFrom);
                        
                        //相差天数
                        int dateDiff = whereParameter.DateDiffs;

                        //对日期字段进行 动态 添加
                        //相差天数是 0 ,是同一天
                        if (whereParameter.DateDiffs == 0)
                        {
                            string str = Common.MyIsDBNull(reaccount, getDateStartTime.ToString("yyyy-MM-dd"));
                            // 日期数据 构成
                            // 含有“割合”的字段内容 Average加上百分号 (日期数据)
                            if (addDate.KindName.Contains("割合"))
                            {
                                if (!string.IsNullOrEmpty(str))
                                {
                                    str += "%";
                                }

                                addDate.OutDateTime.Add(0, str);
                            }
                            //其余的用千分位分割符
                            else
                            {
                                addDate.OutDateTime.Add(0, Common.MyToFormat(reaccount, getDateStartTime.ToString("yyyy-MM-dd"), Common.ClassType.DOUBLE, "#,##0.00"));
                            }
                        }
                        //相差天数大于 0 ,是N天
                        else
                        {
                            string str = null;

                            for (int i = 0; i < dateDiff; i++)
                            {
                                str = string.Empty;
                                //按日期取数据
                                str = Common.MyIsDBNull(reaccount, getDateStartTime.ToString("yyyy-MM-dd"));

                                // 日期数据 构成
                                // 含有“割合”的字段内容 Average加上百分号 (日期数据)
                                if (addDate.KindName.Contains("割合"))
                                {
                                    if (!string.IsNullOrEmpty(str))
                                    {
                                        str += "%";
                                    }

                                    addDate.OutDateTime.Add(i, str);
                                }
                                //其余的用千分位分割符
                                else
                                {
                                    addDate.OutDateTime.Add(i, Common.MyToFormat(reaccount, getDateStartTime.ToString("yyyy-MM-dd"), Common.ClassType.DOUBLE, "#,##0.00"));
                                }
                                getDateStartTime = getDateStartTime.AddDays(1);
                               
                            }
                        }

                        /* Average
                         * 每一行，只有一个Average，所以在日期循环之外添加
                         * 含有“割合”的字段内容 Average加上百分号
                         */
                        if (addDate.KindName.Contains("割合"))
                        {
                            string strAverage = string.Empty;
                            strAverage = Common.MyIsDBNull(reaccount, "Average");
                            if (!string.IsNullOrEmpty(strAverage))
                            {
                                strAverage += "%";
                            }

                            addDate.Average = strAverage;
                        }
                        //其余的用千分位分割符
                        else
                        {
                            addDate.Average = Common.MyToFormat(reaccount, "Average", Common.ClassType.DOUBLE, "#,##0.00");
                        }

                        retFormInforBasedDataList.Add(addDate);
                    }

                    reaccount.Close();
                    conn.Close();
                }
                return retFormInforBasedDataList;
                #endregion SqlCommand
            }
        }
        #endregion 帳票工程別情報
    }
}
